from sqlalchemy import text, desc

from py.PojosAndDaos.Book import Book_class
from py.PojosAndDaos.Record import Record_class


class SummaryService:

    @classmethod
    def Record_Query_All(cls):
        session3 = Record_class().session
        recordList = session3.query(Record_class()).all()
        session3.close()
        return recordList

    # 查询借阅历史
    @classmethod
    def Record_QueryHistory_ByReaderID(cls, readerID: int):
        session3 = Record_class().session
        recordList = session3.query(Record_class()).filter_by(ReaderID=readerID). \
            order_by(desc(Record_class().ISBN)).order_by(desc(Record_class().LendDate)).all()
        session3.close()
        return recordList

    # 查询被借阅历史
    @classmethod
    def RecordBook_QueryHistory_ByISBN(cls, ISBN: int):
        session3 = Record_class().session
        recordList = session3.query(Record_class()).filter_by(ISBN=ISBN). \
            order_by(desc(Record_class().LendDate)).all()

        session3.close()
        return recordList

    @classmethod
    def Record_Query_ByISBN(cls, ISBN: str):
        session3 = Record_class().session
        recordList = session3.query(Record_class()).filter_by(ISBN=ISBN).all()
        session3.close()
        return recordList

    # 查询借阅书目的历史
    @classmethod
    def Record_QueryBook_ByReaderID(cls, readerID: int):
        session3 = Record_class().session
        recordList = session3.query(Record_class()).filter_by(ReaderID=readerID). \
            group_by(Record_class().ISBN).order_by(desc(Record_class().LendDate)).all()
        session3.close()
        return recordList

    # 查询某人当前的借阅书目
    @classmethod
    def RecordNow_QueryBook_ByReaderID(cls, readerID: int):
        session3 = Record_class().session
        recordList = session3.query(Record_class()).filter_by(ReaderID=readerID, isReturned=0).all()
        session3.close()
        return recordList

    @classmethod
    def Record_Delete_ByISBN(cls, ISBN: str):
        session3 = Record_class().session
        session3.query(Record_class()).filter_by(ISBN=ISBN).delete()
        session3.commit()
        session3.close()
        return "记录删除成功！"

    @classmethod
    def Record_Delete_ByReaderID(cls, ReaderID: int):
        session3 = Record_class().session
        session3.query(Record_class()).filter_by(ReaderID=ReaderID).delete()
        session3.commit()
        session3.close()
        return "记录删除成功！"

    @classmethod
    def Book_LentMost(cls):
        session2 = Book_class().session
        bookList = session2.query(Book_class()).order_by(desc(Book_class().count)).limit(3)
        session2.close()
        return bookList


if __name__ == '__main__':
    List = SummaryService.Book_LentMost()
    for book in List:
        print(book.__dict__)
